/*

This program makes Appendix Table A.1

*/




*1) Make Appendix Table A1 (disclosed aggregate data by MNE status)
*******************************************************  
*a) Pull in main disclosed data (made in make_updated_output_01.do)
use "$input/gvc_output.dta", clear

 replace mne_status="DOM" if mne_status=="DOMIMP"

gen mne_st="Domestic" if mne_status=="DOM"
  replace mne_st="Foreign MNEs" if mne_status=="FOR"
  replace mne_st="No foreign manuf affiliates" if mne_status=="MNE"
  replace mne_st="With foreign manuf affiliates" if mne_status=="MNEFORMAN"
  label variable mne_st "Firm Type"
   
   
*c1) Make AL trade variables
gen al_imp=imp_val-rp_imp_value
gen al_exp=exp_val-rp_exp_value

*c2) Make NM variables
foreach vv in emp_man sales_man aff_emp man_aff_emp aff_sales man_aff_sales {
	replace `vv'=0 if `vv'==.
	}
gen sales_global=sales+aff_sales
gen sales_nm=sales-sales_man
gen emp_global=emp+aff_emp
gen emp_nm=emp-emp_man
gen emp_other=emp-emp_man-emp_manage-emp_prof
gen nm_aff_emp=aff_emp-man_aff_emp
gen nm_aff_sales=aff_sales-man_aff_sales
  
   
*d)  Collapse sales, emp, exp, imp over cat
collapse (sum) firms prof_firms emp_global emp emp_man emp_nm emp_prof emp_manage emp_other ///
				sales_global sales sales_man sales_nm importer rp_importer imp_value rp_imp_value ///
				exporter rp_exporter exp_value rp_exp_value  ///
				al_imp al_exp ///
				aff_sales man_aff_sales nm_aff_sales aff_emp man_aff_emp nm_aff_emp, by(type mne_status)	
				
				
			
	
*e) scale things	 
*emp in thousands
foreach var in emp_global emp emp_man emp_nm emp_prof emp_manage emp_other aff_emp man_aff_emp nm_aff_emp {
	gen av_`var'=`var'/firms
    replace `var'=`var'/1000
    }
   
*Sales in billions 
foreach var in sales_global sales sales_man sales_nm aff_sales man_aff_sales nm_aff_sales {
   replace `var'=`var'/1000000
   } 	
 
*Trade in billions  
foreach var in al_imp rp_imp_val al_exp rp_exp_val imp_value exp_value {
   replace `var'=`var'/1000000
   } 

   
*f) make shares
foreach vv in emp_man sales_man emp sales imp_value exp_value {
	egen tot_`vv'=sum(`vv')
	gen double sh_tot_`vv'=round(`vv'/tot_`vv',.01)
	}
	
	

*3) Make full MNE aggregates table 
*******************************************************  
save data/temp.dta, replace
use data/temp.dta, clear


keep type mne_st firms emp_global emp emp_man emp_nm emp_manage emp_prof emp_other sales_global sales sales_man sales_nm ///
imp_value rp_imp_value exp_value rp_exp_value al_imp al_exp ///
aff_sales man_aff_sales nm_aff_sales aff_emp man_aff_emp nm_aff_emp

*consolidate 54 and 55 emp
gen emp_54_5=emp_manage+emp_prof
drop emp_manage emp_prof

*make trade intensity variables
foreach vv in imp exp {
	gen `vv'_sales=`vv'_value/sales
	gen rp_`vv'_sh=rp_`vv'_value/`vv'_value
	}

*make employment shares
gen mp_share=emp_54_5/emp
gen man_share=emp_man/emp
gen man_aff_share=man_aff_emp/aff_emp
gen global_man_share=(man_aff_emp+emp_man)/emp_global
gen us_man_share=emp_man/(emp_man+man_aff_emp)


*make man sales shares
gen man_share_sales=sales_man/sales
gen man_aff_sales_shares=man_aff_sales/aff_sales
gen global_man_sales_share=(man_aff_sales+sales_man)/sales_global
gen us_man_sales_share=sales_man/(sales_man+man_aff_sales)

browse type mne_st us_man_share us_man_sales_share // numbers for share of US MNEs US man share and sales share


foreach vv in firms emp_global emp emp_man emp_nm  emp_54_5 emp_other man_share mp_share sales_global sales sales_man sales_nm man_share_sales ///
imp_value rp_imp_value exp_value rp_exp_value al_imp al_exp imp_sales rp_imp_sh exp_sales rp_exp_sh ///
aff_sales man_aff_sales nm_aff_sales aff_emp man_aff_emp nm_aff_emp man_aff_share man_aff_sales_shares global_man_share global_man_sales_share ///
us_man_share us_man_sales_share {
	rename `vv' val_`vv'
	}
	
reshape long val_, i(type mne_st) j(variable) string

reshape wide val_, i(type variable) j(mne_st) string



gen var_cat=1 if variable=="firms"

replace var_cat=2 if variable=="sales_global"
replace var_cat=3 if variable=="sales"
replace var_cat=4 if variable=="sales_man"
replace var_cat=5 if variable=="sales_nm"
replace var_cat=6 if variable=="aff_sales"
replace var_cat=7 if variable=="man_aff_sales"
replace var_cat=8 if variable=="nm_aff_sales"

replace var_cat=9 if variable=="emp_global"
replace var_cat=10 if variable=="emp"
replace var_cat=11 if variable=="emp_man"
replace var_cat=12 if variable=="emp_54_5"	
replace var_cat=13 if variable=="emp_other"
replace var_cat=14 if variable=="aff_emp"
replace var_cat=15 if variable=="man_aff_emp"	
replace var_cat=16 if variable=="nm_aff_emp"

replace var_cat=17 if variable=="imp_value"
replace var_cat=18 if variable=="al_imp"
replace var_cat=19 if variable=="rp_imp_value"	

replace var_cat=20 if variable=="exp_value"
replace var_cat=21 if variable=="al_exp"
replace var_cat=22 if variable=="rp_exp_value"	

*Adjust labels here to automate
capture label drop var_list
label define var_list 1 "Firms" ///
	2 "Global Sales (\$billions)" 3 "by US Estab" 4 "Manuf" 5 "Non-Manuf" 6 "by Foreign Affiliates" 7 "Manuf" 8 "Non-Manuf" ///
	9 "Global Emp (thousands)" 10 "in US Estabs" 11 "Manuf" 12 "N54 \& 55" 13 "Other"  14 "in Foreign Affiliates" 15 "Manuf" 16 "Non-Manuf" ///
	17 "Imports (\$ billions)" 18 "Arm's-Length" 19 "Related-Party"  20 "Exports (\$ billions)" 21 "Arm's-Length" 22 "Related-Party" 

label values var_cat var_list

label variable val_DOM "Domestic Firms"
label variable val_FOR "Foreign Firms"
label variable val_MNE "US MNEs w/o Foreign Manuf"
label variable val_MNEFORMAN "US MNEs w/Foreign Manuf"
label variable var_cat "Description"

browse var_cat variable val_*

format val* %9.0fc

sort type var_cat
browse type variable var_cat val* if var_cat~=.
export excel type variable var_cat val* if var_cat~=. ///
  using "$app_tables", sheet(TABLE_A1) sheetreplace  firstrow(varlabels) 


*******************************************************  


 